package leaveSystem.dao.mysql;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import leaveSystem.dao.DAOException;
import leaveSystem.dao.EmployeeDAO;
import leaveSystem.model.Employee;

/**
 * @author shwetatripathi
 *
 */
public class EmployeeDAOImpl implements EmployeeDAO {

	private static final String dbUrl = "jdbc:mysql://localhost:3306/leaveapp";
	private static final String dbUserName = "root";
	private static final String dbPassword = "password";
	public Employee createValueObject() {
		return new Employee();
	}
	/*public Employee getObject(Connection conn, int employeeId) throws DAOException, SQLException {

          Employee valueObject = createValueObject();
          valueObject.setEmployeeId(employeeId);
          load(conn, valueObject);
          return valueObject;
    }

    public void load(Connection conn, Employee valueObject) throws DAOException, SQLException {

          String sql = "SELECT * FROM employee WHERE (employeeid = ? ) "; 
          PreparedStatement stmt = null;

          try {
               stmt = conn.prepareStatement(sql);
               stmt.setInt(1, valueObject.getEmployeeId()); 

               singleQuery(conn, stmt, valueObject);

          } finally {
              if (stmt != null)
                  stmt.close();
          }
    }*/

	public List<Employee> loadAll(Connection conn) throws SQLException {

		String sql = "SELECT * FROM leaveapp.employee ORDER BY employeeid ASC ";
		List<Employee> searchResults = listQuery(conn, conn.prepareStatement(sql));

		return searchResults;
	}

	public void insertEmployee(Connection conn, Employee valueObject) throws SQLException {

		String sql = "";
		PreparedStatement stmt = null;
		ResultSet result = null;

		try {
			sql = "INSERT INTO leaveapp.employee ( employeeid, roleid, employeeRank, "
					+ "name, username, password, "
					+ "officialEmail, manager_id, createdBy, "
					+ "createdTimeStamp, updatedBy, updatedTimeStamp) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
			stmt = conn.prepareStatement(sql);

			stmt.setInt(1, valueObject.getEmployeeId()); 
			stmt.setInt(2, valueObject.getRoleId()); 
			stmt.setString(3, valueObject.getEmployeeRank()); 
			stmt.setString(4, valueObject.getName()); 
			stmt.setString(5, valueObject.getUsername()); 
			stmt.setString(6, valueObject.getPassword()); 
			stmt.setString(7, valueObject.getOfficialEmail()); 
			stmt.setInt(8, valueObject.getManagerId()); 
			stmt.setString(9, valueObject.getCreatedBy()); 
			stmt.setDate(10, (Date) valueObject.getCreatedTimeStamp()); 
			stmt.setString(11, valueObject.getUpdatedBy()); 
			stmt.setDate(12, (Date) valueObject.getUpdatedTimeStamp()); 

			int rowcount = databaseUpdate(conn, stmt);
			if (rowcount != 1) {
				//System.out.println("PrimaryKey Error when updating DB!");
				throw new SQLException("PrimaryKey Error when updating DB!");
			}

		} finally {
			if (stmt != null)
				stmt.close();
		}


	}

	public void updateEmployee(Connection conn, Employee valueObject) throws DAOException, SQLException {

		String sql = "UPDATE leaveapp.employee SET roleid = ?, employeeRank = ?, name = ?, "
				+ "username = ?, password = ?, officialEmail = ?, "
				+ "manager_id = ?, createdBy = ?, createdTimeStamp = ?, "
				+ "updatedBy = ?, updatedTimeStamp = ? WHERE (employeeid = ? ) ";
		System.out.println(sql);
		PreparedStatement stmt = null;

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, valueObject.getRoleId()); 
			stmt.setString(2, valueObject.getEmployeeRank()); 
			stmt.setString(3, valueObject.getName()); 
			stmt.setString(4, valueObject.getUsername()); 
			stmt.setString(5, valueObject.getPassword()); 
			stmt.setString(6, valueObject.getOfficialEmail()); 
			stmt.setInt(7, valueObject.getManagerId()); 
			stmt.setString(8, valueObject.getCreatedBy()); 
			stmt.setDate(9, (Date) valueObject.getCreatedTimeStamp()); 
			stmt.setString(10, valueObject.getUpdatedBy()); 
			stmt.setDate(11, (Date) valueObject.getUpdatedTimeStamp()); 

			stmt.setInt(12, valueObject.getEmployeeId()); 
			System.out.println(stmt);

			int rowcount = databaseUpdate(conn, stmt);
			if (rowcount == 0) {
				//System.out.println("Object could not be saved! (PrimaryKey not found)");
				throw new DAOException("Object could not be saved! (PrimaryKey not found)");
			}
			if (rowcount > 1) {
				//System.out.println("PrimaryKey Error when updating DB! (Many objects were affected!)");
				throw new SQLException("PrimaryKey Error when updating DB! (Many objects were affected!)");
			}
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

	public void deleteEmployee(Connection conn, Employee valueObject) throws DAOException, SQLException {

		String sql = "DELETE FROM leaveapp.employee WHERE (employeeid = ? ) ";
		PreparedStatement stmt = null;

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, valueObject.getEmployeeId()); 

			int rowcount = databaseUpdate(conn, stmt);
			if (rowcount == 0) {
				//System.out.println("Object could not be deleted (PrimaryKey not found)");
				throw new DAOException("Object could not be deleted! (PrimaryKey not found)");
			}
			if (rowcount > 1) {
				//System.out.println("PrimaryKey Error when updating DB! (Many objects were deleted!)");
				throw new SQLException("PrimaryKey Error when updating DB! (Many objects were deleted!)");
			}
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

	public void deleteAllEmployee(Connection conn) throws SQLException {

		String sql = "DELETE FROM leaveapp.employee";
		PreparedStatement stmt = null;

		try {
			stmt = conn.prepareStatement(sql);
			int rowcount = databaseUpdate(conn, stmt);
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

	public int countAll(Connection conn) throws SQLException {

		String sql = "SELECT count(*) FROM leaveapp.employee";
		PreparedStatement stmt = null;
		ResultSet result = null;
		int allRows = 0;

		try {
			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();

			if (result.next())
				allRows = result.getInt(1);
		} finally {
			if (result != null)
				result.close();
			if (stmt != null)
				stmt.close();
		}
		return allRows;
	}

	public List<Employee> findEmployee(Connection conn, Employee valueObject) throws SQLException {

		List<Employee> searchResults;

		boolean first = true;
		StringBuffer sql = new StringBuffer("SELECT * FROM leaveapp.employee WHERE 1=1 ");

		if (valueObject.getEmployeeId() != 0) {
			if (first) { first = false; }
			sql.append("AND employeeid = ").append(valueObject.getEmployeeId()).append(" ");
		}

		if (valueObject.getRoleId() != 0) {
			if (first) { first = false; }
			sql.append("AND roleid = ").append(valueObject.getRoleId()).append(" ");
		}

		if (valueObject.getEmployeeRank() != null) {
			if (first) { first = false; }
			sql.append("AND employeeRank LIKE '").append(valueObject.getEmployeeRank()).append("%' ");
		}

		if (valueObject.getName() != null) {
			if (first) { first = false; }
			sql.append("AND name LIKE '").append(valueObject.getName()).append("%' ");
		}

		if (valueObject.getUsername() != null) {
			if (first) { first = false; }
			sql.append("AND username LIKE '").append(valueObject.getUsername()).append("%' ");
		}

		if (valueObject.getPassword() != null) {
			if (first) { first = false; }
			sql.append("AND password LIKE '").append(valueObject.getPassword()).append("%' ");
		}

		if (valueObject.getOfficialEmail() != null) {
			if (first) { first = false; }
			sql.append("AND officialEmail LIKE '").append(valueObject.getOfficialEmail()).append("%' ");
		}

		if (valueObject.getManagerId() != 0) {
			if (first) { first = false; }
			sql.append("AND manager_id = ").append(valueObject.getManagerId()).append(" ");
		}

		if (valueObject.getCreatedBy() != null) {
			if (first) { first = false; }
			sql.append("AND createdBy LIKE '").append(valueObject.getCreatedBy()).append("%' ");
		}

		if (valueObject.getCreatedTimeStamp() != null) {
			if (first) { first = false; }
			sql.append("AND createdTimeStamp = '").append(valueObject.getCreatedTimeStamp()).append("' ");
		}

		if (valueObject.getUpdatedBy() != null) {
			if (first) { first = false; }
			sql.append("AND updatedBy LIKE '").append(valueObject.getUpdatedBy()).append("%' ");
		}

		if (valueObject.getUpdatedTimeStamp() != null) {
			if (first) { first = false; }
			sql.append("AND updatedTimeStamp = '").append(valueObject.getUpdatedTimeStamp()).append("' ");
		}


		sql.append("ORDER BY employeeid ASC ");

		// Prevent accidential full table results.
		// Use loadAll if all rows must be returned.
		if (first)
			searchResults = new ArrayList();
		else
			searchResults = listQuery(conn, conn.prepareStatement(sql.toString()));

		return searchResults;
	}


	public String getDaogenVersion() {
		return " ";
	}

	protected int databaseUpdate(Connection conn, PreparedStatement stmt) throws SQLException {

		int result = stmt.executeUpdate();

		return result;
	}
	/*
    public void singleQuery(Connection conn, PreparedStatement stmt, Employee valueObject) 
          throws DAOException, SQLException {

          ResultSet result = null;

          try {
              result = stmt.executeQuery();

              if (result.next()) {

                   valueObject.setEmployeeId(result.getInt("employeeid")); 
                   valueObject.setRoleId(result.getInt("roleid")); 
                   valueObject.setEmployeeRank(result.getString("employeeRank")); 
                   valueObject.setName(result.getString("name")); 
                   valueObject.setUsername(result.getString("username")); 
                   valueObject.setPassword(result.getString("password")); 
                   valueObject.setOfficialEmail(result.getString("officialEmail")); 
                   valueObject.setManagerId(result.getInt("manager_id")); 
                   valueObject.setCreatedBy(result.getString("createdBy")); 
                   valueObject.setCreatedTimeStamp(result.getDate("createdTimeStamp")); 
                   valueObject.setUpdatedBy(result.getString("updatedBy")); 
                   valueObject.setUpdatedTimeStamp(result.getDate("updatedTimeStamp")); 

              } else {
                    //System.out.println("Employee Object Not Found!");
                    throw new DAOException("Employee Object Not Found!");
              }
          } finally {
              if (result != null)
                  result.close();
              if (stmt != null)
                  stmt.close();
          }
    }*/

	public List<Employee> listQuery(Connection conn, PreparedStatement stmt) throws SQLException {

		ArrayList<Employee> searchResults = new ArrayList<Employee>();
		ResultSet result = null;

		try {
			result = stmt.executeQuery();

			while (result.next()) {
				Employee temp = createValueObject();

				temp.setEmployeeId(result.getInt("employeeid")); 
				temp.setRoleId(result.getInt("roleid")); 
				temp.setEmployeeRank(result.getString("employeeRank")); 
				temp.setName(result.getString("name")); 
				temp.setUsername(result.getString("username")); 
				temp.setPassword(result.getString("password")); 
				temp.setOfficialEmail(result.getString("officialEmail")); 
				temp.setManagerId(result.getInt("manager_id")); 
				temp.setCreatedBy(result.getString("createdBy")); 
				temp.setCreatedTimeStamp(result.getDate("createdTimeStamp")); 
				temp.setUpdatedBy(result.getString("updatedBy")); 
				temp.setUpdatedTimeStamp(result.getDate("updatedTimeStamp")); 

				searchResults.add(temp);
			}

		} finally {
			if (result != null)
				result.close();
			if (stmt != null)
				stmt.close();
		}

		return (List<Employee>)searchResults;
	}


	public ArrayList<Employee> findAllEmployee(Connection conn) throws DAOException {
		ArrayList<Employee> items = new ArrayList<Employee>();

		String selectSql = "SELECT * FROM employee;";

		try {
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				Employee oneEmployee = new Employee();
				oneEmployee.setEmployeeId(rs.getInt(1));
				oneEmployee.setName(rs.getString("name"));
				oneEmployee.setEmployeeRank(rs.getString("employeeRank"));
				items.add(oneEmployee);
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}		
		return items;
	}

	public Employee findEmployeeById(Connection conn,Integer employeeID) throws DAOException {
		String selectSql = "SELECT * FROM leaveapp.employee WHERE employeeid='" + employeeID	+ "';";
System.out.println(selectSql);
		Employee oneEmployee = new Employee();

		try {

			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				oneEmployee.setEmployeeId(rs.getInt(1));
				oneEmployee.setName(rs.getString("name"));
				oneEmployee.setEmployeeRank(rs.getString("employeeRank"));
				oneEmployee.setOfficialEmail(rs.getString("officialEmail"));
				oneEmployee.setRoleId(rs.getInt("roleId"));
				oneEmployee.setPassword(rs.getString("password"));
				oneEmployee.setUsername(rs.getString("username"));
			}
			
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		System.out.println(oneEmployee.toString());
		return oneEmployee;
	}

	public Employee Login(Connection conn,Employee user) throws DAOException, SQLException {
		// TODO Auto-generated method stub
		ResultSet result = null;
		PreparedStatement stmt = null;
		String sql = "SELECT * FROM leaveapp.employee  WHERE (employeeid = 3)";
		stmt = conn.prepareStatement(sql);
		result=stmt.executeQuery(sql);


		if(result!=null){
			user.setValid(true);
			while(result.next())
			{
				user.setRoleId(result.getInt("roleid"));
			}
			return user;
		}

		else{
			user.setValid(false);
			System.out.println("false"+conn);
			return user;
		}

	}

}